<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"><!-- InstanceBegin template="/Templates/notes.dwt" codeOutsideHTMLIsLocked="false" -->
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<!-- InstanceBeginEditable name="doctitle" -->
<title>Kevin's Homepage</title>
<!-- InstanceEndEditable -->
<link href="../main.css" rel="Stylesheet">



<!-- InstanceBeginEditable name="head" --><!-- InstanceEndEditable -->
</head>

<body>

<div id="container">

		<div id="imagehi"><img id="hi" src="../images/hi.gif" alt="picture hi" /></div>
	
  		<div id="rightcolumn">
		<div id="title">
			<h1 id="titletext">Kevin's Home</h1>
		</div>
		
		<ol id="navlinks">
			<li><a href="../index.html">Home</a></li>
			<li><a href="../aboutmyself.html">About Myself</a></li>
			<li><a href="../photoalbum/photoalbum.html">Photo Album</a></li>
			<li><a href="notes.html">Quick Notes</a></li>
			<li class="last"><a href="../links.html">Links</a></li>
		</ol>
		
	    
	<div id="leftbar">  
		<ol>
			<li><a href="c.html">C &amp; C++</a></li>
			<li><a href="java.html">Java</a></li>
			<li><a href="perl.html">Perl</a></li>
			<li><a href="centos.html">Centos</a></li>
			<li><a href="unix.html">Unix</a></li>
			<li><a href="css.html">CSS</a></li>
			<li><a href="php.html">PHP</a></li>
			<li><a href="sql.html">SQL</a></li>
			<li><a href="photoshop.html">Photoshop</a></li>
			<li><a href="windows.html">Windows</a></li>
			<li><a href="numbers.html">Numbers</a></li>
			<li><a href="misc.html">Misc</a></li>
		</ol>
	</div><!-- end of leftbar -->
	    <!-- InstanceBeginEditable name="EditRegion_submain" -->
	    <div id="main">
        <h3>SQL (Structured Query Language) </h3>
		<h4>Basic SQL commands:</h4>
		<table class="utable">
		<tbody>
			<tr>
			  <td>SELECT * FROM table WHERE column1='some text' ORDER BY column1, column2 ASC;</td>
			  <td>SELECT statement</td>
		  </tr>
			<tr>
			  <td>INSERT INTO table (column1, column2, column3) VALUES (val1, val2, val3);</td>
			  <td>INSERT statement</td>
		  </tr>
			<tr>
			  <td>UPDATE table SET column1='some text' WHERE id='123';</td>
			  <td>UPDATE statement</td>
		  </tr>
			<tr>
			  <td>DELETE FROM table where id='123';</td>
			  <td>DELETE statement, *use single quote in SQL*</td>
		  </tr>
			<tr>
			  <td>CREATE DATABASE database1;</td>
			  <td>CREATE DATABASE</td>
		  </tr>
			<tr>
			  <td>CREATE TABLE table1 {<br />
			    id int(11) NOT NULL auto_increment,<br />
		        menu_name varchar(30) NOT NULL,<br />
		          position int(3) NOT NULL,<br />
		          visible tinyint(1) NOT NULL,<br />
		          PRIMARY KEY (id),
	            <br />
		      };</td>
			  <td>CREATE TABLE</td>
		  </tr>
			<tr>
			  <td>create user kevin identified by abc123<br />
			    default tablespace DATA<br />
			    temporary tablespace TEMP<br />
			    quota unlimited on DATA<br />
			    quota unlimited on INDX<br />
			    quota unlimited on OBJECTS<br />
			    quota unlimited on TEMP; </td>
			  <td>create user kevin with password &quot;abc123&quot; </td>
		  </tr>
			<tr>
			  <td>grant unlimited tablespace to kevin; <br />
			    grant query rewrite to kevin; </td>
			  <td>grant privileges </td>
		  </tr>
			<tr>
			  <td>revoke unlimited tablespace from kevin; <br />
revoke query rewrite from kevin;</td>
			  <td>revoke privileges from user kevin </td>
		  </tr>
			<tr>
			  <td>connect sys@ntserver as sysdba<br />
			    Enter password:****** </td>
			  <td>connect to database </td>
		  </tr>
			<tr>
			  <td>create role DEVELOP; <br />
			    select * from role_sys_privs; </td>
			  <td>create a role and check role privileges </td>
		  </tr>
			<tr>
			  <td>select * from session_roles; <br />
			    set role none; <br />
			    set role DEVELOP; </td>
			  <td>disable all roles and enable role DEVELOP </td>
		  </tr>
			<tr>
			  <td>desc user_role_privs; <br />
			    select user_name,granted_role from user_role_privs;<br />
			    desc user_sys_privs;<br />
				desc user_tab_privs; </td>
			  <td>describle user privileges</td>
		  </tr>
			<tr>
				<td>select table_name from user_tables; <br />
				  select table_name from dba_tables;<br />
				  select table_name from all_tables; </td>
				<td>check table names in the database </td>
			</tr>
			<tr>
			  <td>show all; </td>
			  <td>show all the environment settings that can be changed </td>
		  </tr>
			<tr>
			  <td>set wrap off;<br />
		      show wrap; </td>
			  <td>no wrap when display results </td>
		  </tr>
			<tr>
			  <td>set linesize 130;<br />
		      show linesize; </td>
			  <td>set display result line length to 130 </td>
		  </tr>
			<tr>
			  <td>desc venue; </td>
			  <td>describle column information of table venue </td>
		  </tr>
		  <tr>
			  <td>delete * from venue;<br />
			    commit;</td>
			  <td>delete everything from table venue </td>
		  </tr>
			<tr>
			  <td>select username from user_users; </td>
			  <td>check current user of the database </td>
		  </tr>
		  <tr>
			  <td>CREATE TALBE venue (<br />
				  &nbsp;&nbsp;venue_id NUMBER NOT NULL,<br />
				  &nbsp;&nbsp;name VARCHAR2(64) NULL,<br />
				  &nbsp;&nbsp;city VARCHAR2(32) NULL<br />
				  CONSTRAINT XPKVenue<br />
				  &nbsp;&nbsp;PRIMARY KEY (venue_id),<br />
				  CONSTRAINT AK_Venue_1<br />
				  &nbsp;&nbsp;UNIQUE (<br />
				  &nbsp;&nbsp;&nbsp;&nbsp;name<br />
				  &nbsp;&nbsp;)<br />
		        );			    </td>
			  <td>create table venue </td>
		  </tr>
		  <tr>
		    <td>CREATE TABLE SupportingAct (<br />
		       &nbsp;&nbsp;act_id NUMBER NOT NULL,<br /> 
	          &nbsp;&nbsp;show_id NUMBER NOT NULL, <br />
	          CONSTRAINT XPKSupportingAct<br /> 
	          &nbsp;&nbsp;PRIMARY KEY (act_id, show_id),<br />
	          CONSTRAINT R_193<br /> 
	          &nbsp;&nbsp;FOREIGN KEY (act_id)<br />
			  &nbsp;&nbsp;&nbsp;&nbsp;REFERENCES Act,<br />
			  CONSTRAINT R_194<br /> 
			  &nbsp;&nbsp;FOREIGN KEY (show_id)<br />
	          &nbsp;&nbsp;REFERENCES Show<br />
	        )</td>
		    <td>create table SupportingAct, act_id must exist in Act table and show_id must exist in Show table </td>
	      </tr>
		  <tr>
		    <td>CREATE GLOBAL TEMPORARY TABLE <br />
		    &nbsp;&nbsp;shows (act varchar2(64),<br />
	        &nbsp;&nbsp;venue varchar2(64)) <br />
	        ON COMMIT PRESERVE ROWS; <br />
	        INSERT INTO shows (act, venue) <br />
	        select a.name as Act,<br />
	        v.name as Venue <br />
	        from act a,venue v, shows s <br />
	        where s.act_id=a.act.id and<br />
	        s.venue_id=v.venue_id;</td>
		    <td>create temporary table </td>
	      </tr>
		  <tr>
			  <td>alter table act add (detail varchar2(64)); </td>
			  <td>add column to table act </td>
		  </tr>
		  <tr>
		    <td>alter table act modify (detail not null); </td>
		    <td>modify column property </td>
	      </tr>
		  <tr>
		    <td>alter table act drop (detail); </td>
		    <td>drop table column </td>
	      </tr>
		  <tr>
			  <td>drop table venue; </td>
			  <td>remove talbe venue from database </td>
		  </tr>
		  <tr>
		    <td>alter table act rename to acts; </td>
		    <td>rename table name </td>
	      </tr>
		  <tr>
		    <td>alter user kevin identified by abc123; </td>
		    <td>change user kevin's password to abc123 </td>
	      </tr>
		  <tr>
		    <td>drop user kevin; <br />
		      drop user kevin cascade; </td>
		    <td>drop user by DBA, 'cascade' means any data associated to kevin will also be dropped </td>
	      </tr>
		  <tr>
		    <td>spool c:\tmp\out.txt;<br />
		      select * from venue;<br />
		      spool off; </td>
		    <td>output the statement results to log file </td>
	      </tr>
		  <tr>
		    <td>select a.name,b.name from category a, category b where a.parent_id is not null and a.parent_id=b.category_id;</td>
		    <td>select statement to retrive data, selecting the same table 'category' twice</td>
	      </tr>
		  <tr>
		    <td>select * from user_tables where table_name in ('act'); <br />
	        select * from user_tables where table_name in ('ACT');</td>
		    <td>using small case 'act' will get no results, must use upper case 'ACT' , because all metadata are stored uppercase internally </td>
	      </tr>
		  <tr>
		    <td>/</td>
		    <td>re-execute the last SQL command in SQLPlus </td>
	      </tr>
		  <tr>
		    <td>select * from category where name like 'A%'; </td>
		    <td>filtered query </td>
	      </tr>
		  <tr>
		    <td>select * from category order by name; <br />
	        select * from category order by name desc; <br />
	        select showdate from show order by showdate  nulls first; </td>
		    <td>sorted query (ascending and descending), if results contain NULL, it is considered as highest value; use 'nulls first' to list null value in first</td>
	      </tr>
		  <tr>
		    <td>select a.name, v.name from act a, show s, venue v where a.act_id=s.act_id and v.venue_id=s.venue_id; </td>
		    <td>join statement </td>
	      </tr>
		  <tr>
		    <td>select s.show_id, (select name from category where category_id = s.category_id) from show s; </td>
		    <td>subquery</td>
	      </tr>
		  <tr>
		    <td>select act_id as id, name as Act from act; </td>
		    <td>change display heading for column names </td>
	      </tr>
		  <tr>
		    <td>select category_id, parent_id from category where parent_id is null and (category_id = 1 or category_id=2); </td>
		    <td>filter select statement with where clause </td>
	      </tr>
		  <tr>
		    <td>select act_id from act where act_id in (1,2,3,4,5); <br />
	        select act_id from act where act_id not in (1,2,3,4,5);</td>
		    <td>in</td>
	      </tr>
		  <tr>
		    <td>select category_id from category where category between 1 and 10; </td>
		    <td>between</td>
	      </tr>
		  <tr>
		    <td>select a.act_id from act a where exists (select supportingact_id from supportingact where supportingact_id=a.act_id); </td>
		    <td>exists</td>
	      </tr>
		</tbody>
		</table>
		
		<h4>Oracle 10g on windows</h4>
		<table class="utable">
		<tbody>
			<tr>
				<td><a href="http://localhost:5500/em">http://localhost:5500/em</a></td>
				<td>enterprise manager</td>
			</tr>
			<tr>
				<td><a href="http://localhost:5620/ultrasearch">http://localhost:5620/ultrasearch</a><br />
				<a href="http://localhost:5620/ultrasearch/admin">http://localhost:5620/ultrasearch/admin</a></td>
				<td>ultra search</td>
			</tr>
			<tr>
				<td><a href="http://localhost:5560/isqlplus">http://localhost:5560/isqlplus</a></td>
				<td>iSQLPlus</td>
			</tr>
		</tbody>
		</table>

		<h4>Setting the environment (commonly used), Oracle 10g</h4>
		<table class="utable">
		<tbody>
			<tr>
				<td>echo [ON | OFF]</td>
				<td>echo commands to the screen </td>
			</tr>
			<tr>
				<td>escape [ON | OFF | char] </td>
				<td>sets the escape character (default is \) </td>
			</tr>
			<tr>
				<td>feedback [ON | OFF] </td>
				<td>display rows returned </td>
			</tr>
			<tr>
			  <td>heading [ON | OFF] </td>
			  <td>display column headings </td>
		  </tr>
			<tr>
			  <td>linesize</td>
			  <td>characters in line before a newline is printed </td>
		  </tr>
			<tr>
			  <td>numformat $999,999.90 </td>
			  <td>set the default numerical format </td>
		  </tr>
			<tr>
			  <td>numwidth n </td>
			  <td>default width for number is 10 characters </td>
		  </tr>
			<tr>
			  <td>NULL 'string' </td>
			  <td>replace NULL values with a string </td>
		  </tr>
			<tr>
			  <td>pagesize n </td>
			  <td>set to 0 to suppress headers, breaks and titles </td>
		  </tr>
			<tr>
			  <td>serveroutput [ON | OFF] </td>
			  <td>used with DBMS_OUTPUT to display output in PL/SQL code </td>
		  </tr>
			<tr>
			  <td>sqlprompt 'string' </td>
			  <td>set as '' to remove prompt </td>
		  </tr>
			<tr>
			  <td>termout [ON | OFF] </td>
			  <td>suppresses screen output </td>
		  </tr>
			<tr>
			  <td>timing [ON | OFF] </td>
			  <td>switches timing of each command on and off </td>
		  </tr>
			<tr>
			  <td>verify [ON | OFF] </td>
			  <td>variable substitution display </td>
		  </tr>
			<tr>
			  <td>wrap [ON | OFF] </td>
			  <td>line word wrapping </td>
		  </tr>
			<tr>
			  <td>spool [ON | OFF] </td>
			  <td>switch on or off output log file </td>
		  </tr>
		</tbody>
		</table>		
	    </div>
    <!-- InstanceEndEditable --></div><!-- end of rightcolumn div -->
</div><!-- end of container div -->

<div id="footer">
	<p>Website design by <a href="../index.html">Kevin Mu Qing</a>; Hosting by <a href="http://www.jimlei.com" target="_blank">Jim Xianyu Lei</a></p>
</div>

</body>
<!-- InstanceEnd --></html>
